

* Descriptive statistics *

* 1) Agency level * 

clear all
set more off

global work `"F:\fiscal research\mayor\grant"' 
cd "${work}"

use "F:\fiscal research\mayor\PSRM data\PSRM_fed_grant_city_FY2005_2021_desc1.dta", clear



log using "F:\fiscal research\mayor\PSRM data\PSRM_descstat1_log"

** Log file for Analysis: Figure 1, Figure A.4, Figure A.5, Table B.1 **



* Figure 1-(a)
sort year_monthD
twoway line fed_act_yr_mth_mil year_monthD, ///
	legend(label(1 "Federal Action Obligation")) /// 
	xscale(noline) yscale(noline)	///	
	ytitle("Grant ($ million)") xtitle("Date") ///
	ylabel( ,angle(horizontal))	///
	tlabel(01oct2005(365)30sep2021, angle(45),) 
graph export "grant_yr_mth.png", as(png) replace



* Figure 1-(b)
sort award_dept_acr_up

graph bar fed_act_agency_mil, ///
over (award_dept_acr_up, label(labsize(vsmall) angle(65)) sort(fed_act_agency_mil) descending) ///
legend(label(1 "Grant")) ylabel(,labsize(small)) ytitle("") ///
ytitle("Grant ($ million)") b1title("Agency") 
graph export "grant_agency.png", as(png) replace


* Figure A.4
sort fed_act_mth_mil
graph bar fed_act_mth_mil, ///
over (action_date_fiscal_mon, label(labsize(vsmall) angle(65))) ///
legend(label(1 "Grant")) ylabel(,labsize(small)) ytitle("") ///
ytitle("Grant ($ million)") b1title("Month") 
graph export "grant_mth.png", as(png) replace


drop if cfda_number1==".."


bysort awarding_agency_name cfda_number1: keep if _n==1


gen dept=awarding_agency_name
replace dept="Others" if inlist(dept, "CORPORATION FOR NATIONAL AND COMMUNITY SERVICE (CNCS)", "ELECTION ASSISTANCE COMMISSION (EAC)", "EXECUTIVE OFFICE OF THE PRESIDENT (EOP)")
replace dept="Others" if inlist(dept, "INSTITUTE OF MUSEUM AND LIBRARY SERVICES (IMLS)", "NATIONAL AERONAUTICS AND SPACE ADMINISTRATION (NASA)", "NATIONAL ARCHIVES AND RECORDS ADMINISTRATION (NARA)")
replace dept="Others" if inlist(dept, "NATIONAL ENDOWMENT FOR THE ARTS (NEA)", "SMALL BUSINESS ADMINISTRATION (SBA)", "SOCIAL SECURITY ADMINISTRATION (SSA)", "SURFACE TRANSPORTATION BOARD (STB)")



split dept, p("(")
split dept2, p(")")



replace dept21="Others" if missing(dept21) 

* replace dept21="Others" if inlist(dept21, "DOD", "VA", "TREAS")



* Table B.1 - Column 1, 2
drop if missing(cfda_number1)
drop if cfda_number1==".."


bysort awarding_agency_name cfda_number1: keep if _n==1 // * total number of grants: 701

gen grant=1

bysort awarding_agency_name: egen grant_count=total(grant)

tabstat grant_count, by(awarding_agency_name) stat(n) // column 1

gen grant_count_rat=grant_count/701

tabstat grant_count_rat, by(awarding_agency_name) stat(mean) // column 2



* Figure A.5 
graph pie, over(dept21) sort descending legend(rows(3) size(*.7)) plabel(_all percent, color(white) size(*0.6) format(%2.1f))
 
graph export "grant_agency_pie.png", as(png) replace




bysort awarding_agency_name: keep if _n==1 


* Table B.1 - Column 3, 4


gen grant_agen_sum=sum(fed_act_agency_mil) // 57264.12

gen fed_act_agency_mil_rat=fed_act_agency_mil/57264.12

tabstat fed_act_agency_mil, by(awarding_agency_name) stat(mean) // column 3

tabstat fed_act_agency_mil_rat, by(awarding_agency_name) stat(mean) // column 4


 
 

log close
 
translate "F:\fiscal research\mayor\PSRM data\PSRM_descstat1_log.smcl" "F:\fiscal research\mayor\PSRM data\PSRM_descstat1_log.pdf" 







**
* 1) City level * 
clear all
set more off

global work `"F:\fiscal research\mayor\grant"' 
cd "${work}"

use "F:\fiscal research\mayor\PSRM data\PSRM_fed_grant_city_FY2005_2021_desc2.dta", clear



log using "F:\fiscal research\mayor\PSRM data\PSRM_descstat2_log"

** Log file for Analysis: Table B.3, Figure 2, Figure 3, Figure A.2, Figure A.3 **




* Table B.3
tabstat fed_act_yr_city_2012_pc mayor_pres_party strong_mayor_pres_party mayor_pres_gover_party str_mayor_pres_gover_party pres_elec_yr tot_pop income_pc pov_rate, col(stat) stat(n min max mean sd)




gen city_name_st = city_name + " (" + stabbr + ")"

* Figure 2
sort primary_place_of_performance_cit

graph bar fed_act_city_mil if fed_act_city_mil>250 & !missing(fed_act_city_mil), ///
over (city_name_st, label(labsize(vsmall) angle(65)) sort(fed_act_city_mil) descending) ///
legend(label(1 "Grant")) ylabel(,labsize(small)) ytitle("") ///
ytitle("Grant ($ million)") b1title("City") 
graph export "grant_city.png", as(png) replace


* Figure 3-(a)
graph bar fed_act_yr_city_pc if year==2019 & fed_act_yr_city_2012_pc>80 & !missing(fed_act_yr_city_2012_pc), ///
over (city_name_st, label(labsize(vsmall) angle(65)) sort(fed_act_yr_city_2012_pc) descending) ///
legend(label(1 "Grant")) ylabel(,labsize(small)) ytitle("") ///
ytitle("Grant ($ Per Capita)") b1title("City") 
graph export "grant_city_pc_fy2020.png", as(png) replace


* Figure 3-(b)
graph bar fed_act_yr_city_pc if year==2020 & fed_act_yr_city_2012_pc>80 & !missing(fed_act_yr_city_2012_pc), ///
over (city_name_st, label(labsize(vsmall) angle(65)) sort(fed_act_yr_city_2012_pc) descending) ///
legend(label(1 "Grant")) ylabel(,labsize(small)) ytitle("") ///
ytitle("Grant ($ Per Capita)") b1title("City") 
graph export "grant_city_pc_fy2021.png", as(png) replace


* Housing

gen fed_act_house_yr_mil=fed_act_house_yr/1000000

gen fed_act_house_city_mil=fed_act_house_city/1000000

sort year city_name

* Figure A.2-(a)
graph bar fed_act_house_city_mil if fed_act_house_city_mil>30 & !missing(fed_act_house_city_mil), ///
over (city_name_st, label(labsize(vsmall) angle(65)) sort(fed_act_house_city_mil) descending) ///
legend(label(1 "Grant")) ylabel(,labsize(small)) ytitle("") ///
ytitle("Grant ($ million)") b1title("City") 
graph export "grant_city_house.png", as(png) replace


bysort geo_fips: egen sum_fed_act_house_yr_city_pc=total(fed_act_house_yr_city_pc) if !missing(fed_act_house_yr_city_pc)

* Figure A.2-(b)
graph bar sum_fed_act_house_yr_city_pc if sum_fed_act_house_yr_city_pc>100 & !missing(sum_fed_act_house_yr_city_pc), ///
over (city_name_st, label(labsize(vsmall) angle(65)) sort(sum_fed_act_house_yr_city_pc) descending) ///
legend(label(1 "Grant")) ylabel(,labsize(small)) ytitle("") ///
ytitle("$") b1title("City") 
graph export "grant_city_house_pc.png", as(png) replace


* Transportation
gen fed_act_transport_yr_mil=fed_act_transport_yr/1000000

gen fed_act_transport_city_mil=fed_act_transport_city/1000000

sort year city_name

* Figure A.3-(a)
graph bar fed_act_transport_city_mil if fed_act_transport_city_mil>100 & !missing(fed_act_transport_city_mil), ///
over (city_name_st, label(labsize(vsmall) angle(65)) sort(fed_act_transport_city_mil) descending) ///
legend(label(1 "Grant")) ylabel(,labsize(small)) ytitle("") ///
ytitle("Grant ($ million)") b1title("City") 
graph export "grant_city_transportation.png", as(png) replace


bysort geo_fips: egen sum_fed_act_trans_yr_city_pc=total(fed_act_trans_yr_city_pc) if !missing(fed_act_trans_yr_city_pc)

* Figure A.3-(b)
graph bar sum_fed_act_trans_yr_city_pc if sum_fed_act_trans_yr_city_pc>400 & !missing(sum_fed_act_trans_yr_city_pc), ///
over (city_name_st, label(labsize(vsmall) angle(65)) sort(sum_fed_act_trans_yr_city_pc) descending) ///
legend(label(1 "Grant")) ylabel(,labsize(small)) ytitle("") ///
ytitle("$") b1title("City") 
graph export "grant_city_transportation_pc.png", as(png) replace


 
log close
 
translate "F:\fiscal research\mayor\PSRM data\PSRM_descstat2_log.smcl" "F:\fiscal research\mayor\PSRM data\PSRM_descstat2_log.pdf" 









**
* 1) City level * 
clear all
set more off

global work `"F:\fiscal research\mayor\grant"' 
cd "${work}"

use "F:\fiscal research\mayor\PSRM data\PSRM_fed_grant_city_FY2005_2021_desc2.dta", clear



log using "F:\fiscal research\mayor\PSRM data\PSRM_descstat3_log"

** Log file for Analysis: Table B.2 **



** Table B.2

gen panel=1
bysort geo_fips: egen panel_count=total(panel)
bysort state_fips: egen state_city_count=total(panel)
bysort state_fips: egen state_mayor_pres_count=total(panel) if mayor_pres_party==1
bysort state_fips: egen state_sec_mayor_pres_count=total(panel) if strong_mayor_pres_party==1
bysort state_fips: egen state_mayor_pres_gov_count=total(panel) if mayor_pres_gover_party==1
bysort state_fips: egen state_sec_may_pres_gov_cnt=total(panel) if str_mayor_pres_gover_party==1


foreach x in state_city_count state_mayor_pres_count state_sec_mayor_pres_count state_mayor_pres_gov_count state_sec_may_pres_gov_cnt{
	replace `x' = 0 if missing(`x')
}


foreach x in state_city_count state_mayor_pres_count state_sec_mayor_pres_count state_mayor_pres_gov_count state_sec_may_pres_gov_cnt{
	egen `x'_max = max(`x'), by(state_fips)
}

foreach x in state_city_count_max state_mayor_pres_count_max state_sec_mayor_pres_count_max state_mayor_pres_gov_count_max state_sec_may_pres_gov_cnt_max{
	replace `x' = 0 if missing(`x')
}

drop state_city_count state_mayor_pres_count state_sec_mayor_pres_count state_mayor_pres_gov_count state_sec_may_pres_gov_cnt

rename (*_max) (*)



tab primary_place_of_performance_sta mayor_pres_party, freq 


tabstat mayor_pres_party, by(primary_place_of_performance_sta) stat(n) // column 3

tabstat mayor_pres_party if mayor_pres_party==1, by(primary_place_of_performance_sta) stat(n) // column 4

tabstat mayor_pres_party if strong_mayor_pres_party==1, by(primary_place_of_performance_sta) stat(n) // column 5

tabstat mayor_pres_party if mayor_pres_gover_party==1, by(primary_place_of_performance_sta) stat(n) // column 6

tabstat mayor_pres_party if str_mayor_pres_gover_party==1, by(primary_place_of_performance_sta) stat(n) // column 7


bysort geo_fips: keep if _n==1 // * Count city list: 569

gen city=1

bysort state_fips: egen city_count=total(city) 

gen city_count_rat=city_count/569


tabstat city_count, by(primary_place_of_performance_sta) stat(n) // column 1

tabstat city_count_rat, by(primary_place_of_performance_sta) stat(mean) // column 2




 
log close
 
translate "F:\fiscal research\mayor\PSRM data\PSRM_descstat3_log.smcl" "F:\fiscal research\mayor\PSRM data\PSRM_descstat3_log.pdf" 





